package com.example.mapper;

import org.apache.ibatis.annotations.*;
import com.example.pojo.User;
import com.example.pojo.UserVo;

import java.util.List;


public interface UserMapper {
    //alter+enter , 底层反射+注解
    //@Select("select * from tbl_user")
    List<User> findAll();


    //#{占位符名称}应该和@Param("变量名")保持一致
    @Select("select * from tbl_user where username = #{uname} and password = #{pwd}")
    User findByUsernameAndPwd(@Param("uname") String username, @Param("pwd") String pwd);

    /**
     * 保证对象的属性名和占位符的名称高度保持一致.
     *
     * @param user
     * @return 受影响行数
     */
    @Insert("insert into tbl_user(username,password,email,img_url,power) values(#{username},#{password},#{email},#{imgUrl},#{power})")
    int save(User user);

    /**
     * 物理删除,实际开发中推荐使用逻辑删除.
     * 逻辑删除就是给表再增加一列status.  如果status=1,表示正常,status=0,表示删除.
     *
     * @param id
     * @return
     */
    //@Delete("delete from tbl_user where id = #{id}")
    int delById(Integer id);

    /**
     * id是不需要哦更新的
     *
     * @param user
     * @return
     */
    @Update("update tbl_user set username = #{username},password = #{password},email = #{email},img_url = #{imgUrl},power = #{power} where id = #{id}")
    int update(User user);

    /**
     * 使用第三方实体类来封装我们查询结果
     *
     * @return
     */
    @Select("select power,count(id) power_count from tbl_user group by power")
    List<UserVo> groupByPower();

    @Select("select * from tbl_user where id = #{id}")
    User getById(Integer id);


    // 根据用户名查用户/登录（注册或校验是否存在）
    @Select("SELECT * FROM tbl_user WHERE username = #{username}")
    User findByUsername(String username);

    boolean existsByUsername(String username);


    //根据邮箱登录
    @Select("SELECT * FROM tbl_user WHERE email = #{email}")
    User findByEmail(String email);

    // 保存用户信息，img_url 和 power 默认值为 '1'
    @Insert("INSERT INTO tbl_user (username, password, email, img_url, power) " +
            "VALUES (#{username}, #{password}, #{email}, '1', '1')")
    int insertUser(User user);
    // 分页模糊查询，只查username, email, power字段
    @Select("SELECT id, username, email, power FROM tbl_user WHERE username LIKE CONCAT('%', #{keyword}, '%') LIMIT #{offset}, #{pageSize}")
    List<User> searchUsers(@Param("keyword") String keyword, @Param("offset") int offset, @Param("pageSize") int pageSize);

    @Select("SELECT COUNT(*) FROM tbl_user WHERE username LIKE CONCAT('%', #{keyword}, '%')")
    int countUsers(@Param("keyword") String keyword);

    // 插入只插入 username, email, power，password和imgUrl不处理
    @Insert("INSERT INTO tbl_user(username, email, power) VALUES(#{username}, #{email}, #{power})")
    @Options(useGeneratedKeys = true, keyProperty = "id") // 插入后返回主键id
    int insertUUser(User user);

    // 根据id删除
    @Delete("DELETE FROM tbl_user WHERE id = #{id}")
    int deleteUser(Integer id);


}
